# This file is used to create the user_info_ext table in the database.

'''
-- 用户信息扩展表
CREATE TABLE user_info_ext (
    user_id INTEGER PRIMARY KEY REFERENCES user_info(user_id),
    real_name VARCHAR(20) NOT NULL,
    email VARCHAR(50) NOT NULL, 
    address VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    sex VARCHAR(10) NOT NULL,
    avatar VARCHAR(200) NOT NULL
);
'''

class UserInfoExtTableMethod: # 用户信息扩展表方法类

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    def insert_user_info_ext(self, user_id, real_name, email, address, birthday, sex, avatar): # 插入用户信息扩展表

        sql = "INSERT INTO user_info_ext (user_id, real_name, email, address, birthday, sex, avatar) VALUES (%s, %s, %s, %s, %s, %s, %s)"
        params = (user_id, real_name, email, address, birthday, sex, avatar)
        try:
            self.cursor.execute(sql, params) # 执行sql语句 
            self.conn.commit() # 提交事务 
            return True # 插入成功  
        except Exception as e:  
            print(e)  
            return False # 插入失败 
        
    def insert_or_update_user_info_ext(self, user_id, real_name, email, address, birthday, sex, avatar): # 插入用户信息扩展表

        if self.select_user_info_ext(user_id) is None: # 用户信息扩展表中不存在该用户信息
            
            result = self.insert_user_info_ext(user_id, real_name, email, address, birthday, sex, avatar) # 插入用户信息扩展表
        
        else: # 用户信息扩展表中存在该用户信息

            result = self.update_user_info_ext(user_id, real_name, email, address, birthday, sex, avatar) # 更新用户信息扩展表
    
        return result # 返回插入或更新结果 
    
    def select_user_info_ext(self, user_id): # 查询用户信息扩展表

        sql = "SELECT * FROM user_info_ext WHERE user_id = %s"
        params = (user_id,)
        try:
            self.cursor.execute(sql, params) # 执行sql语句 
            result = self.cursor.fetchone() # 获取查询结果 
            return result # 返回查询结果 
        except Exception as e:  
            print(e)   
            return None # 查询失败 
        
    def update_user_info_ext(self, user_id, real_name, email, address, birthday, sex, avatar): # 更新用户信息扩展表 

        sql = "UPDATE user_info_ext SET real_name = %s, email = %s, address = %s, birthday = %s, sex = %s, avatar = %s WHERE user_id = %s" 
        params = (real_name, email, address, birthday, sex, avatar, user_id) 
        try:
            self.cursor.execute(sql, params) # 执行sql语句 
            self.conn.commit() # 提交事务 
            return True # 更新成功  
        except Exception as e:  
            print(e)  
            return False # 更新失败 
    
    def delete_user_info_ext(self, user_id): # 删除用户信息扩展表 

        sql = "DELETE FROM user_info_ext WHERE user_id = %s"
        params = (user_id,)
        try:
            self.cursor.execute(sql, params) # 执行sql语句 
            self.conn.commit() # 提交事务 
            return True # 删除成功  
        except Exception as e:  
            print(e)  
            return False # 删除失败 